<?php
/**
 * Created by PhpStorm.
 * User: 刘抱
 * Date: 2018/10/25
 * Time: 10:02
 */

namespace Home\Controller;

use PHPExcel_Style_Alignment;

class ReportController extends CommunalController
{
    public function index($excel = null, $cdepcode = null, $iyear = null)
    {
		parent::check_permission('CRMG-B');//权限管报
		$db_u8_report_config = M("db_u8_report_config",'',C('DB_CONFIG2'));
        $db_u8_detail = M("db_u8_detail",'',C('DB_CONFIG2'));
        $db_department_mapping = M("db_department_mapping",'',C('DB_CONFIG2'));
        $where = array();
        $cdepcode = isset($cdepcode) ? $cdepcode : I('cdepcode');
        $iyear = isset($iyear) ? $iyear : '2018';
        if (!empty($cdepcode)){
			$check_department = $db_department_mapping->field('u8_deptcodes,u8_projectcodes')->where("id={$cdepcode}")->find();
			if(!empty($check_department['u8_deptcodes'])){
				$where['cdepcode'] = array('in',$check_department['u8_deptcodes']);
			}
			if(!empty($check_department['u8_projectcodes'])){
				$where['citem_id'] = array('in',$check_department['u8_projectcodes']);
			}
		}
        if (!empty($iyear)) $where['iyear'] = $iyear;

        $res_department_mapping = $db_department_mapping->order('sort asc')->select();//EHR部门

        $res_config_level1 = $db_u8_report_config->where("level=1")->select();//一级
        $res_config_level2 = $db_u8_report_config->where("level=2")->select();//二级

        $subject_codes = ReportController::getSubjectCodes($res_config_level2);
        $where_detail['ccode'] = array('in', $subject_codes);
        $where['remark'] = array('exp',"not like '%其他%'");
        $res_detail = $db_u8_detail->field('iperiod,sum(md) as sum,ccode,iyear')->where($where_detail)->where($where)->group('iperiod,ccode')->select();
        foreach ($res_config_level2 as $key => $value) {
            for ($i = 1; $i <= 12; $i++) {
                $res_config_level2[$key][$i] = '';
                foreach ($res_detail as $item) {
                    if (in_array($item['ccode'], explode(',', $value['subject_codes'])) && $item['iperiod'] == $i) {
                        $res_config_level2[$key][$i] = $item['sum'];
                    }
                    $res_config_level2[$key]['iyear'] = $item['iyear'];
                }
                $res_config_level2[$key]['sum'] += $res_config_level2[$key][$i];
            }
        }
        //把二级数组插入一级数组
        foreach ($res_config_level1 as $key => $value) {
            $res_config_level1[$key]['iyear'] = $iyear;
            foreach ($res_config_level2 as $item) {
                if ($item['parent_id'] == $value['report_config_id']) {
                    $res_config_level1[$key]['level2'][] = $item;
                    for ($i = 1; $i <= 12; $i++) {
                        $res_config_level1[$key][$i] += $item[$i];
                    }
                    $res_config_level1[$key]['sum'] += $item['sum'];
                }

            }
        }
        if (isset($excel) && $excel == 1) {
            return $res_config_level1;
            die;
        }
        $this->assign('result', $res_config_level1);
        $this->assign('res_department_mapping', $res_department_mapping);
        $this->assign('cdepcode', $cdepcode);
        $this->assign('iyear', $iyear);
        $this->display();
    }
	public function checkDay($date){
		$firstday = date('Y-m-01', strtotime($date));
		$lastday = date('Y-m-d', strtotime("$firstday +1 month -1 day"));
		return array($firstday,$lastday);
	}
	public function checkYear($date){
		$sdate = explode("-",$date);
		$re['iyear']=$sdate[0];
		if($sdate[1][0]==0){
			$re['iperiod']= $sdate[1][1];
		}else{
			$re['iperiod']= $sdate[1];
		}
		return $re;
	}
	public function checkSubject($sub){
		$db = M("db_u8_report_config",'',C('DB_CONFIG2'));
		$where="";
		$where['subject_codes']=$sub;
		$result = $db->field('report_config_id')->where($where)->find();

		$list = $db->field('subject_codes')->where("parent_id={$result['report_config_id']} and subject_codes is not null")->select();
		$str=array();
		foreach($list as $l){
			array_push($str,$l['subject_codes']);
		}
		return $str;
	}

	public function detail()
	{
		parent::check_permission('CRMG-B');//权限管报
		$where="";
		$db_detail=M("db_u8_detail",'',C('DB_CONFIG2'));
		$db_mapping=M("db_department_mapping",'',C('DB_CONFIG2'));
		//处理列表过来的2个情况
		//科目+全年
		if(!empty($_REQUEST['subject'])){
			$subject = $_REQUEST['subject'];
			if(strpos($subject,',')){
				$where['ccode']=array('in',$subject);
			}else{
				$spl_subject=array(6001,6403,6601,6602,6603,6301,6711,6801,6401);
				if(in_array($subject,$spl_subject)){
					$re = $this->checkSubject($subject);
					$where['ccode']=array('in',implode(",",$re));
				}else{
					$where['ccode']=array('in',$subject);
				}
			}
			if(!empty($_REQUEST['year'])){
				$where['iyear']=$_REQUEST['year'];
				$this->assign('start',$_REQUEST['year']."-01");
				$this->assign('end',$_REQUEST['year']."-12");
			}
			if(!empty($_REQUEST['ehr_department_id'])){
				$dept_map = $db_mapping->field('u8_deptcodes,u8_projectcodes')->where("id={$_REQUEST['ehr_department_id']}")->find();
				if(!empty($dept_map['u8_deptcodes'])){
					$where['cdepcode'] = array('in',$dept_map['u8_deptcodes']);
				}
				if(!empty($dept_map['u8_projectcodes'])){
					$where['citem_id'] = array('in',$dept_map['u8_projectcodes']);
				}
			}
			if($_REQUEST['iperiod']!='all' && $_REQUEST['iperiod']!=''){
				$iperiod = explode("-",$_REQUEST['iperiod']);
				$where['iyear']=$iperiod[0];
				$where['iperiod']=$iperiod[1];
				if(strlen($iperiod[1])==1){
					$getime= $iperiod[0]."-0".$iperiod[1];
				}else{
					$getime= $iperiod[0]."-".$iperiod[1];
				}
				// $day=$this->checkDay($_REQUEST['iperiod']);
				$this->assign('start',$getime);
				$this->assign('end',$getime);
			}
		}
		// if(!empty($_REQUEST['check_subject'])){
			// $check_subject=$_REQUEST['check_subject'];
		// }else{
			// $check_subject=-10;
		// }
		// $this->assign('check_subject',$check_subject);
		// if(!empty($_REQUEST['check_deptment'])){
			// $check_deptment=$_REQUEST['check_deptment'];
		// }else{
			// $check_deptment=-10;
		// }
		// $this->assign('check_deptment',$check_deptment);
		// if(!empty($_REQUEST['check_project'])){
			// $check_project=$_REQUEST['check_project'];
		// }else{
			// $check_project=-10;
		// }
		// $this->assign('check_project',$check_project);
		// if(!empty($_REQUEST['use'])){
			// $map['cbill']=array('like','%'.$_REQUEST['use'].'%');
			// $map['ccheck']=array('like','%'.$_REQUEST['use'].'%');
			// $map['cbook']=array('like','%'.$_REQUEST['use'].'%');
			// $map['_logic']='or';
			// $where['_complex']=$map;
			// $this->assign('use',$_REQUEST['use']);
		// }
		// if(!empty($_REQUEST['remark'])){
			// $where['remark']=array('like','%'.$_REQUEST['remark'].'%');
			// $this->assign('remark',$_REQUEST['remark']);
		// }
		$where['_string']="1=1";
		if(!empty($_REQUEST['start_date'])){
			$where['_string']="dbill_date>='".$_REQUEST['start_date']."'";
			$this->assign('start',$_REQUEST['start_date']);
		}
		// else{
			// $start = date('Y-m',strtotime("-1 month"));
			// $this->assign('start',$start);
		// }
		if(!empty($_REQUEST['end_date'])){
			if($where['_string']=="1=1"){
				$where['_string']="dbill_date<='".$_REQUEST['end_date']."'";
			}else{
				$where['_string']=$where['_string']." and dbill_date<='".$_REQUEST['end_date']."'";
			}
			$this->assign('end',$_REQUEST['end_date']);
		}
		// }else{
			// $end = date('Y-m',strtotime("-1 month"));
			// $str= $this->checkYear($end);
			// $where['iyear']=$str['iyear'];
			// $where['iperiod']= $str['iperiod'];
			// $this->assign('end',$end);
		// }
        // if($check_subject!=-10){$where['ccode']=$check_subject;}
        // if($check_deptment!=-10){$where['cdepcode']=$check_deptment;}
        // if($check_project!=-10){$where['citem_id']=$check_project;}
        $field="iperiod,iyear,csign,ino_id,dbill_date,cbill,ccheck,cbook,remark,cdepcode,citem_id,ccode,ccode_name,md,cdepname,cinvname";
        $result = $db_detail->field($field)->where($where)->order('i_id desc')->select();
        $showlist = $this->show_page($result);
        //科目
        $db_subject=M("db_u8_subject",'',C('DB_CONFIG2'));
        $subject_list = $db_subject->field('ccode,ccode_name')->order('SubId desc')->select();
        //部门
        $db_deptment = M("db_u8_deptment",'',C('DB_CONFIG2'));
        $deptment_list = $db_deptment->field('cdepcode,cdepname')->order('cdepcode desc')->select();
        //项目
        $db_project = M("db_u8_project",'',C('DB_CONFIG2'));
        $project_list = $db_project->field('cinvcode,cinvname')->order('cinvcode desc')->select();
        $this->assign('page',$showlist['show']);
        $this->assign('result',$showlist['result']);
        $this->assign('subject_list',$subject_list);
        $this->assign('deptment_list',$deptment_list);
        $this->assign('project_list',$project_list);
        $this->display();
    }


    /**
     * //处理三维数组拿到最小维的subject_codes集合
     * @param $array
     * @return array
     */
    static function getSubjectCodes($array)
    {
        $subject_codes = array_unique(array_column($array, 'subject_codes'));
        foreach ($subject_codes as $k => $v) {
            if (empty($v)) {
                unset($subject_codes[$k]);
            }
        }
        $subject_codes = explode(',', implode(',', $subject_codes));
        return $subject_codes;
    }

	
    public function excel()
    {
		parent::check_permission('CRMG-B');//权限管报
        $cdepcode = I('cdepcode');
        $iyear = I('iyear');
        $new = array();
        if (!empty($cdepcode)) {
            $department_mapping = M("db_department_mapping",'',C('DB_CONFIG2'))->field('ehr_deptname')->where("id=$cdepcode")->find();
            $result = $this->index('1', $cdepcode, $iyear);
            $new[$department_mapping['ehr_deptname']] = $result;
        }else{
            $department_mapping = M("db_department_mapping",'',C('DB_CONFIG2'))->field('id,ehr_deptname')->select();
            foreach ($department_mapping as $map_key=>$map_val){
                $result = $this->index('1', $map_val['id'], $iyear);
                $new[$map_val['ehr_deptname']] = $result;
            }
        }

        import("Org.Util.PHPExcel");
        import("Org.Util.PHPExcel.Writer.Excel5");
        import("Org.Util.PHPExcel.IOFactory.php");
        $objPHPExcel = new \PHPExcel();
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);//设置保存版本格式
        //合并单元格
        /*$objPHPExcel->getActiveSheet()->mergeCells('A1:F1');
        $objPHPExcel->getActiveSheet()->mergeCells('A2:F2');*/

        //写数据到表格里面去
        $level_key2 = 2;//从第二行开始插入
        $sheet = 0;
        foreach ($new as $new_key => $res) {
            $objPHPExcel->setactivesheetindex($sheet);
            $objPHPExcel->createSheet();
            $objPHPExcel->getActiveSheet()->setTitle($new_key);
            //设置表头行高
            $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(35);
            $objPHPExcel->getActiveSheet()->getStyle('A1:N1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
            $objPHPExcel->getActiveSheet()->getStyle('A1:N1')->getFill()->getStartColor()->setARGB(\PHPExcel_Style_Color::COLOR_BLACK);
            //设置字体颜色
            $objPHPExcel->getActiveSheet()->getStyle('A1:N1')->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_WHITE);
            //设置font
            $objPHPExcel->getActiveSheet()->getStyle('A:N')->getFont()->setName('Microsoft YaHei UI');
            $objPHPExcel->getActiveSheet()->getStyle('A1:N1')->getFont()->setSize(16);
            $objPHPExcel->getActiveSheet()->getStyle('A:N')->getFont()->setSize(12);
            $objPHPExcel->getActiveSheet()->getStyle('A')->getFont()->setBold(true);

            $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);//设置默认行高
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(50);//设置列的宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);//设置列的宽度
            //设置表头对齐方式
            //$objPHPExcel->getActiveSheet()->getStyle('A1:A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //$objPHPExcel->getActiveSheet()->getStyle('A1:A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            //设置水平左对齐
            $objPHPExcel->getActiveSheet()->getStyle('A:N')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
            //所有垂直居中
            $objPHPExcel->getActiveSheet()->getStyle('A:N')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            //填入主标题
            $objPHPExcel->getActiveSheet()->setCellValue('A1', '科目');
            $objPHPExcel->getActiveSheet()->setCellValue('N1', '合计');
            $num = 1;
            for ($i = 'B'; $i <= 'M'; $i++) {
                $objPHPExcel->getActiveSheet()->setCellValue($i . '1', $num . '月');//循环插入表头月份填入表头
                $objPHPExcel->getActiveSheet()->getStyle($i . '1')->getFont()->setSize(16);//循环把表头文字变大
                $objPHPExcel->getActiveSheet()->getColumnDimension($i)->setWidth(20);//设置列的宽度
                do {
                    $num = $num + 1;
                } while (0);
            }
            foreach ($res as $key => $value) {
                $level_key = 1;//每次循环时最小维初始变为1
                $objPHPExcel->getActiveSheet()->getStyle('A' . $level_key2)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
                $objPHPExcel->getActiveSheet()->getStyle('N' . $level_key2)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
                $objPHPExcel->getActiveSheet()->getStyle('A' . $level_key2)->getFill()->getStartColor()->setARGB(\PHPExcel_Style_Color::COLOR_DARKGREEN);
                $objPHPExcel->getActiveSheet()->getStyle('N' . $level_key2)->getFill()->getStartColor()->setARGB(\PHPExcel_Style_Color::COLOR_DARKGREEN);
                //设置字体颜色
                $objPHPExcel->getActiveSheet()->getStyle('A' . $level_key2)->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_WHITE);
                $objPHPExcel->getActiveSheet()->getStyle('N' . $level_key2)->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_WHITE);
                $objPHPExcel->getActiveSheet()->setCellValue('A' . $level_key2, $value['report_subject_name']);
                $objPHPExcel->getActiveSheet()->setCellValue('N' . $level_key2, $value['sum']);
                for ($i = 'B'; $i <= 'M'; $i++) {
                    $objPHPExcel->getActiveSheet()->getStyle($i . $level_key2)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
                    $objPHPExcel->getActiveSheet()->getStyle($i . $level_key2)->getFill()->getStartColor()->setARGB(\PHPExcel_Style_Color::COLOR_DARKGREEN);
                    $objPHPExcel->getActiveSheet()->getStyle($i . $level_key2)->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_WHITE);
                    $objPHPExcel->getActiveSheet()->setCellValue($i . $level_key2, $value[$level_key]);
                    $level_key++;
                }
                $level_key2 = $level_key2 + 1;
                foreach ($value['level2'] as $k => $v) {
                    $objPHPExcel->getActiveSheet()->setCellValue('A' . $level_key2, ' └' . $v['report_subject_name']);
                    $objPHPExcel->getActiveSheet()->setCellValue('B' . $level_key2, $v['1']);
                    $objPHPExcel->getActiveSheet()->setCellValue('C' . $level_key2, $v['2']);
                    $objPHPExcel->getActiveSheet()->setCellValue('D' . $level_key2, $v['3']);
                    $objPHPExcel->getActiveSheet()->setCellValue('E' . $level_key2, $v['4']);
                    $objPHPExcel->getActiveSheet()->setCellValue('F' . $level_key2, $v['5']);
                    $objPHPExcel->getActiveSheet()->setCellValue('G' . $level_key2, $v['6']);
                    $objPHPExcel->getActiveSheet()->setCellValue('H' . $level_key2, $v['7']);
                    $objPHPExcel->getActiveSheet()->setCellValue('I' . $level_key2, $v['8']);
                    $objPHPExcel->getActiveSheet()->setCellValue('J' . $level_key2, $v['9']);
                    $objPHPExcel->getActiveSheet()->setCellValue('K' . $level_key2, $v['10']);
                    $objPHPExcel->getActiveSheet()->setCellValue('L' . $level_key2, $v['11']);
                    $objPHPExcel->getActiveSheet()->setCellValue('M' . $level_key2, $v['12']);
                    $objPHPExcel->getActiveSheet()->setCellValue('N' . $level_key2, $v['sum']);
                    $level_key2 = $level_key2 + 1;
                }
            }
            $sheet++;
            $level_key2=2;
        }
        //下载这个表格，在浏览器输出
        $filename = '报表';
        $outputFileName = $filename . ".xls";
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:attachment;filename="' . $outputFileName . '"');  //到文件
        //header(‘Content-Disposition:inline;filename="‘.$outputFileName.‘"‘);  //到浏览器
        header("Content-Transfer-Encoding: binary");
        header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        ob_end_clean();
        $objWriter->save('php://output');
    }

}